PL/PGSQL Function problem. - Mailing list pgsql-sql
From | Dirk Elmendorf |
---|---|
Subject | PL/PGSQL Function problem. |
Date | |
Msg-id | p04310104b5bf2d8e41a1@[64.39.0.183] Whole thread Raw |
Responses |
Re: PL/PGSQL Function problem.
|
List | pgsql-sql |
RedHat 6.2 /Postgres 6.53 I'm still very new at PL/PGSQL but I have looked thru all the documentation I could find before sending this in. I have a function that I would like to be able to apply to multiple tables without having to duplicate the code. I would like to be able to pass in the table name I would like to apply the function to. I cannot seem to get this to work. Below is what I have so far. I would like to be able to have computers_equal(comp_one,comp_two,comp_table_one,comp_table_two); I cannot seem to find a way to pass in a text string to be used in the select into statements. Anyone got any ideas on how I fix this other that writing a function for all the permutations (ordered_parts vs used_parts , ordered_parts vs new_parts, used_parts vs ordered_parts, new_parts vs ordered_parts,new_parts vs used_parts, used_parts vs ordered_parts) Any advice would be appreciated. create table ordered_parts (computer_number int4,part_number int4 ); create table used_parts (computer_number int4,part_number int4 ); create tabel new_parts (computer_number int4,part_number int4 ); insert into ordered_parts values(1,401); insert into ordered_parts values(1,402); insert into used_parts values(2,401); insert into used_parts values(2,402); insert into used_parts values(3,401); insert into used_parts values(3,403); insert into new_parts values(4,401); insert into new_parts values(4,402); insert into new_parts values(5,401); insert into new_parts values(5,403); CREATE FUNCTION computers_equal(int4 , int4) RETURNS bool AS 'DECLARE true CONSTANT bool DEFAULT ''t''; false CONSTANTbool DEFAULT ''f''; parts_list RECORD;BEGIN SELECT INTO parts_list part_number,part_label from ordered_parts where computer_number = $1 except select part_number,part_label from used_parts where computer_number= $2; IF NOT FOUND THEN -- now test the reverse SELECT INTO parts_list part_number,part_label from used_parts where computer_number = $2 except select part_number,part_label from ordered_parts where computer_number= $1; IF NOT FOUND THEN RETURN true; ELSE RETURN false; END IF; ELSE RETURN false; END IF; END; ' LANGUAGE 'plpgsql'; --This will list all the comptuers that match select distinct on computer_number computer_number from used_parts where computers_equal(1,computer_number) and computer_number!=1; /* --this doesn't work CREATE FUNCTION computers_equal(int4 , int4,text,text) RETURNS bool AS 'DECLARE true CONSTANT bool DEFAULT ''t''; falseCONSTANT bool DEFAULT ''f''; parts_list RECORD;BEGIN SELECT INTO parts_list part_number,part_label from $3 where computer_number = $1 except select part_number,part_label from $4 where computer_number= $2; IF NOT FOUND THEN -- now test the reverse SELECT INTO parts_list part_number,part_label from $4 where computer_number = $2 except select part_number,part_label from $3 where computer_number= $1; IF NOT FOUND THEN RETURN true; ELSE RETURN false; END IF; ELSE RETURN false; END IF; END; ' LANGUAGE 'plpgsql'; select distinct on computer_number computer_number from used_parts where computers_equal(1,computer_number,ordered_parts,used_parts) and computer_number!=1; */ -- ______________________________________________________________________ Dirk Elmendorf, CTE Main: 210-892-4000 Rackspace Managed Hosting Weston Center Fax: 210-892-4329 112 East Pecan, Suite 600 Email:dirk@rackspace.com San Antonio, TX 78205 <http://www.rackspace.com>